DROP TRIGGER TRG_BLOG_POST_ID
;


DROP SEQUENCE SEQ_BLOG_POST_ID
;


DROP TABLE BLOG_POST CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_BLOG_THEME_ID
;


DROP SEQUENCE SEQ_BLOG_THEME_ID
;


DROP TABLE BLOG_THEME CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_COURSE_ID
;


DROP SEQUENCE SEQ_COURSE_ID
;


DROP TABLE COURSE CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_COURSE_TEMA_ID
;


DROP SEQUENCE SEQ_COURSE_TEMA_ID
;


DROP TABLE COURSE_TEMA CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_COURSE_VIDEO_ID
;


DROP SEQUENCE SEQ_COURSE_VIDEO_ID
;


DROP TABLE COURSE_VIDEO CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_MATERIAL_ID
;


DROP SEQUENCE SEQ_MATERIAL_ID
;


DROP TABLE MATERIAL CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_ONLINE_CLASS_ID
;


DROP SEQUENCE SEQ_ONLINE_CLASS_ID
;


DROP TABLE ONLINE_CLASS CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_REALIDAD_AUMENTADA_ID
;


DROP SEQUENCE SEQ_REALIDAD_AUMENTADA_ID
;


DROP TABLE REALIDAD_AUMENTADA CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_ROLE_ID
;


DROP SEQUENCE SEQ_ROLE_ID
;


DROP TABLE ROLE CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_USER_COURSE_ID
;


DROP SEQUENCE SEQ_USER_COURSE_ID
;


DROP TABLE USER_COURSE CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_USERS_ID
;


DROP SEQUENCE SEQ_USERS_ID
;


DROP TABLE USERS CASCADE CONSTRAINTS
;
DROP TRIGGER TRG_USERS_ROLE_ID
;


DROP SEQUENCE SEQ_USERS_ROLE_ID
;


DROP TABLE USERS_ROLE CASCADE CONSTRAINTS
;

CREATE TABLE BLOG_POST
(
	ID            NUMBER(38) NOT NULL,
	THEME_ID      NUMBER(38),
	USER_ID       NUMBER(38),
	POST_CONTENT  VARCHAR2(1000),
	DATE_CREATE   DATE NOT NULL,
	DATE_UPDATE   DATE,
	VERSION       NUMBER(38)
)
;


CREATE TABLE BLOG_THEME
(
	ID           NUMBER(38) NOT NULL,
	THEME_NAME   VARCHAR2(500) NOT NULL,
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE COURSE
(
	ID             NUMBER(38) NOT NULL,
	COURSE_NAME    VARCHAR2(255),
	COURSE_DESC    VARCHAR2(2000) NOT NULL,
	COURSE_THEMES  VARCHAR2(2000) NOT NULL,
	COURSE_IMAGE   VARCHAR2(255) NOT NULL,
	DATE_CREATE    DATE NOT NULL,
	DATE_UPDATE    DATE,
	VERSION        NUMBER(38)
)
;


CREATE TABLE COURSE_TEMA
(
	ID           NUMBER(38) NOT NULL,
	COURSE_ID    NUMBER(38),
	TEMA_NAME    VARCHAR2(255) NOT NULL,
	TEMA_DESC    VARCHAR2(1000),
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE COURSE_VIDEO
(
	ID           NUMBER(38) NOT NULL,
	COURSE_ID    NUMBER(38),
	VIDEO_NAME   VARCHAR2(255) NOT NULL,
	VIDEO_DESC   VARCHAR2(1000) NOT NULL,
	VIDEO_PATH   VARCHAR2(255) NOT NULL,
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE MATERIAL
(
	ID             NUMBER(38) NOT NULL,
	COURSE_ID      NUMBER(38),
	MATERIAL_NAME  VARCHAR2(255) NOT NULL,
	MATERIAL_DESC  VARCHAR2(2000) NOT NULL,
	MATERIAL_PATH  VARCHAR2(255) NOT NULL,
	DATE_CREATE    DATE NOT NULL,
	DATE_UPDATE    DATE,
	VERSION        NUMBER(38)
)
;


CREATE TABLE ONLINE_CLASS
(
	ID           NUMBER(38) NOT NULL,
	COURSE_ID    NUMBER(38),
	CLASS_TITLE  VARCHAR2(100) NOT NULL,
	CLASS_DESC   VARCHAR2(2000) NOT NULL,
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE REALIDAD_AUMENTADA
(
	ID           NUMBER(38) NOT NULL,
	COURSE_ID    NUMBER(38),
	RA_NAME      VARCHAR2(255),
	RA_DESC      VARCHAR2(2000) NOT NULL,
	RA_PATH      VARCHAR2(255) NOT NULL,
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE ROLE
(
	ID           NUMBER(38) NOT NULL,
	ROLE_NAME    VARCHAR2(30) NOT NULL,
	DATE_CREATE  DATE NOT NULL,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE USER_COURSE
(
	ID           NUMBER(38) NOT NULL,
	USER_ID      NUMBER(38) NOT NULL,
	COURSE_ID    NUMBER(38) NOT NULL,
	DATE_BEGIN   DATE,
	DATE_END     DATE,
	DATE_CREATE  DATE,
	DATE_UPDATE  DATE,
	VERSION      NUMBER(38)
)
;


CREATE TABLE USERS
(
	ID               NUMBER(38) NOT NULL,
	USER_FIRST_NAME  VARCHAR2(30) NOT NULL,
	USER_LAST_NAME   VARCHAR2(30) NOT NULL,
	USER_EMAIL       VARCHAR2(50) NOT NULL,
	USER_LOGIN_NAME  VARCHAR2(50) NOT NULL,
	USER_PASSWORD    VARCHAR2(255) NOT NULL,
	USER_STATUS      NUMBER(1),
	DATE_CREATE      DATE NOT NULL,
	DATE_UPDATE      DATE,
	VERSION          NUMBER(38)
)
;


CREATE TABLE USERS_ROLE
(
	ID       NUMBER(38) NOT NULL,
	ROLE_ID  NUMBER(38) NOT NULL,
	USER_ID  NUMBER(38) NOT NULL,
	VERSION  NUMBER(38)
)
;



ALTER TABLE BLOG_POST ADD CONSTRAINT PK_BLOG_POST 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE BLOG_THEME ADD CONSTRAINT PK_BLOG_THEME 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE COURSE ADD CONSTRAINT PK_COURSE 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE COURSE_TEMA ADD CONSTRAINT PK_COURSE_TEMA 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE COURSE_VIDEO ADD CONSTRAINT PK_EVORY_VIDEOS 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE MATERIAL ADD CONSTRAINT PK_MATERIAL 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE ONLINE_CLASS ADD CONSTRAINT PK_ONLINE_CLASS 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE REALIDAD_AUMENTADA ADD CONSTRAINT PK_REALIDAD_AUMENTADA 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE ROLE ADD CONSTRAINT PK_ROLE 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE USER_COURSE ADD CONSTRAINT PK_USER_COURSE 
	PRIMARY KEY (ID, USER_ID, COURSE_ID) 
 USING INDEX 
;

ALTER TABLE USERS ADD CONSTRAINT PK_USUARIO 
	PRIMARY KEY (ID) 
 USING INDEX 
;

ALTER TABLE USERS_ROLE ADD CONSTRAINT PK_USER_ROLE 
	PRIMARY KEY (ID, ROLE_ID, USER_ID) 
 USING INDEX 
;


ALTER TABLE ROLE
	ADD CONSTRAINT UQ_ROLE_ROLE_NAME UNIQUE (ROLE_NAME)
 USING INDEX 
;

ALTER TABLE USERS
	ADD CONSTRAINT UQ_USER_USER_EMAIL UNIQUE (USER_EMAIL)
 USING INDEX 
;

ALTER TABLE USERS
	ADD CONSTRAINT UQ_USER_USER_LOGIN_NAME UNIQUE (USER_LOGIN_NAME)
 USING INDEX 
;


ALTER TABLE BLOG_POST ADD CONSTRAINT FK_BLOG_POST_BLOG_THEME 
	FOREIGN KEY (THEME_ID) REFERENCES BLOG_THEME (ID)
ON DELETE CASCADE
;

ALTER TABLE BLOG_POST ADD CONSTRAINT FK_BLOG_POST_USER 
	FOREIGN KEY (USER_ID) REFERENCES USERS (ID)
ON DELETE CASCADE
;

ALTER TABLE COURSE_TEMA ADD CONSTRAINT FK_COURSE_TEMA_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE COURSE_VIDEO ADD CONSTRAINT FK_COURSE_VIDEOS_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE MATERIAL ADD CONSTRAINT FK_MATERIAL_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE ONLINE_CLASS ADD CONSTRAINT FK_ONLINE_CLASS_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE REALIDAD_AUMENTADA ADD CONSTRAINT FK_REALIDAD_AUMENTADA_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE USER_COURSE ADD CONSTRAINT FK_USER_COURSE_COURSE 
	FOREIGN KEY (COURSE_ID) REFERENCES COURSE (ID)
ON DELETE CASCADE
;

ALTER TABLE USER_COURSE ADD CONSTRAINT FK_USER_COURSE_USER 
	FOREIGN KEY (USER_ID) REFERENCES USERS (ID)
ON DELETE CASCADE
;

ALTER TABLE USERS_ROLE ADD CONSTRAINT FK_USER_ROLE_ROLE 
	FOREIGN KEY (ROLE_ID) REFERENCES ROLE (ID)
ON DELETE CASCADE
;

ALTER TABLE USERS_ROLE ADD CONSTRAINT FK_USER_ROLE_USER 
	FOREIGN KEY (USER_ID) REFERENCES USERS (ID)
ON DELETE CASCADE
;






CREATE SEQUENCE SEQ_BLOG_POST_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_BLOG_POST_ID 
	BEFORE INSERT 
	ON BLOG_POST 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_BLOG_POST_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_BLOG_THEME_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_BLOG_THEME_ID 
	BEFORE INSERT 
	ON BLOG_THEME 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_BLOG_THEME_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_COURSE_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_COURSE_ID 
	BEFORE INSERT 
	ON COURSE 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_COURSE_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_COURSE_TEMA_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_COURSE_TEMA_ID 
	BEFORE INSERT 
	ON COURSE_TEMA 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_COURSE_TEMA_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_COURSE_VIDEO_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_COURSE_VIDEO_ID 
	BEFORE INSERT 
	ON COURSE_VIDEO 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_COURSE_VIDEO_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_MATERIAL_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_MATERIAL_ID 
	BEFORE INSERT 
	ON MATERIAL 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_MATERIAL_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_ONLINE_CLASS_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_ONLINE_CLASS_ID 
	BEFORE INSERT 
	ON ONLINE_CLASS 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_ONLINE_CLASS_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_REALIDAD_AUMENTADA_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_REALIDAD_AUMENTADA_ID 
	BEFORE INSERT 
	ON REALIDAD_AUMENTADA 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_REALIDAD_AUMENTADA_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_ROLE_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_ROLE_ID 
	BEFORE INSERT 
	ON ROLE 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_ROLE_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_USER_COURSE_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_USER_COURSE_ID 
	BEFORE INSERT 
	ON USER_COURSE 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_USER_COURSE_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_USERS_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_USERS_ID 
	BEFORE INSERT 
	ON USERS 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_USERS_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;


CREATE SEQUENCE SEQ_USERS_ROLE_ID 
    INCREMENT BY 1 
    START WITH 1 
    NOMAXVALUE 
    MINVALUE 1 
	NOCYCLE 
	NOCACHE 
	NOORDER
;



CREATE OR REPLACE TRIGGER TRG_USERS_ROLE_ID 
	BEFORE INSERT 
	ON USERS_ROLE 
	FOR EACH ROW 
	BEGIN 
		SELECT SEQ_USERS_ROLE_ID.NEXTVAL 
		INTO :NEW.ID 
		FROM DUAL; 
	END;
;
